-- 版本：V2.0.0
-- 描述：语音识别模块双用户体系迁移脚本
-- 日期：2024-12-19
-- 作者：Voice Note API Team
-- 说明：将现有的语音识别系统升级为支持双用户体系（系统用户 + 客户端用户）

BEGIN;

-- ======================================
-- 第一部分：创建新表
-- ======================================

-- 1. 创建客户端用户表
CREATE TABLE IF NOT EXISTS speech_client_user (
    -- 主键
    id BIGSERIAL PRIMARY KEY,
    
    -- 基本信息
    user_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    company VARCHAR(200),
    contact_person VARCHAR(100),
    phone VARCHAR(20),
    
    -- 客户端类型和状态
    client_type VARCHAR(20) DEFAULT 'personal',
    status VARCHAR(20) DEFAULT 'active',
    
    -- API认证信息
    api_key VARCHAR(64) NOT NULL UNIQUE,
    api_secret_hash VARCHAR(128) NOT NULL,
    
    -- 配额管理
    monthly_quota INTEGER DEFAULT 1000,
    used_quota INTEGER DEFAULT 0,
    quota_reset_date DATE,
    
    -- 权限配置
    ip_whitelist JSONB,
    allowed_models JSONB,
    allowed_features JSONB,
    
    -- API限制
    rate_limit_per_minute INTEGER DEFAULT 60,
    rate_limit_per_hour INTEGER DEFAULT 1000,
    
    -- 回调配置
    webhook_url VARCHAR(500),
    webhook_secret VARCHAR(128),
    webhook_events JSONB,
    
    -- 统计信息
    total_requests BIGINT DEFAULT 0,
    total_duration DECIMAL(15,3) DEFAULT 0,
    last_request_time TIMESTAMP,
    
    -- 审计字段
    create_by VARCHAR(64) DEFAULT '',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64) DEFAULT '',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 软删除标志
    del_flag SMALLINT DEFAULT 0,
    
    -- 备注
    remark VARCHAR(500) DEFAULT NULL
);

-- 2. 创建客户端使用统计表
CREATE TABLE IF NOT EXISTS speech_client_usage (
    -- 主键
    id BIGSERIAL PRIMARY KEY,
    
    -- 关联信息
    client_user_id BIGINT NOT NULL,
    
    -- 统计日期
    usage_date DATE NOT NULL,
    
    -- 使用统计
    request_count INTEGER DEFAULT 0,
    success_count INTEGER DEFAULT 0,
    failed_count INTEGER DEFAULT 0,
    
    -- 时长统计
    total_duration DECIMAL(10,3) DEFAULT 0,
    total_file_size BIGINT DEFAULT 0,
    
    -- 模型使用统计
    model_usage JSONB,
    
    -- 语言使用统计
    language_usage JSONB,
    
    -- 计费信息
    billing_amount DECIMAL(10,4) DEFAULT 0,
    billing_unit VARCHAR(20) DEFAULT 'minute',
    
    -- 性能统计
    avg_processing_time DECIMAL(10,3),
    avg_confidence DECIMAL(5,4),
    
    -- 错误统计
    error_types JSONB,
    
    -- 审计字段
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 唯一约束
    UNIQUE(client_user_id, usage_date)
);

-- ======================================
-- 第二部分：修改现有表结构
-- ======================================

-- 1. 为 speech_record 表添加新字段
DO $$
BEGIN
    -- 添加客户端用户ID字段
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'speech_record' AND column_name = 'client_user_id') THEN
        ALTER TABLE speech_record ADD COLUMN client_user_id BIGINT;
    END IF;
    
    -- 添加用户类型字段
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'speech_record' AND column_name = 'user_type') THEN
        ALTER TABLE speech_record ADD COLUMN user_type VARCHAR(20) DEFAULT 'system';
    END IF;
    
    -- 添加API请求ID字段
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'speech_record' AND column_name = 'api_request_id') THEN
        ALTER TABLE speech_record ADD COLUMN api_request_id VARCHAR(64);
    END IF;
    
    -- 添加回调URL字段
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'speech_record' AND column_name = 'callback_url') THEN
        ALTER TABLE speech_record ADD COLUMN callback_url VARCHAR(500);
    END IF;
    
    -- 添加回调状态字段
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'speech_record' AND column_name = 'callback_status') THEN
        ALTER TABLE speech_record ADD COLUMN callback_status VARCHAR(20);
    END IF;
    
    -- 添加回调尝试次数字段
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'speech_record' AND column_name = 'callback_attempts') THEN
        ALTER TABLE speech_record ADD COLUMN callback_attempts INTEGER DEFAULT 0;
    END IF;
    
    -- 添加会话ID字段
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'speech_record' AND column_name = 'session_id') THEN
        ALTER TABLE speech_record ADD COLUMN session_id VARCHAR(64);
    END IF;
END $$;

-- 2. 更新现有记录的用户类型
UPDATE speech_record 
SET user_type = 'system' 
WHERE user_type IS NULL OR user_type = '';

-- 3. 设置用户类型字段为非空
ALTER TABLE speech_record ALTER COLUMN user_type SET NOT NULL;

-- ======================================
-- 第三部分：创建索引
-- ======================================

-- speech_record 表新增索引
CREATE INDEX IF NOT EXISTS idx_speech_record_client_user_id ON speech_record (client_user_id) WHERE del_flag = 0;
CREATE INDEX IF NOT EXISTS idx_speech_record_user_type ON speech_record (user_type);
CREATE INDEX IF NOT EXISTS idx_speech_record_api_request_id ON speech_record (api_request_id) WHERE api_request_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_speech_record_session_id ON speech_record (session_id) WHERE session_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_speech_record_callback_status ON speech_record (callback_status) WHERE callback_status IS NOT NULL;

-- 复合索引
CREATE INDEX IF NOT EXISTS idx_speech_record_client_time ON speech_record (client_user_id, create_time) WHERE del_flag = 0;

-- speech_client_user 表索引
CREATE UNIQUE INDEX IF NOT EXISTS uk_speech_client_user_api_key ON speech_client_user (api_key) WHERE del_flag = 0;
CREATE UNIQUE INDEX IF NOT EXISTS uk_speech_client_user_email ON speech_client_user (email) WHERE del_flag = 0;
CREATE INDEX IF NOT EXISTS idx_speech_client_user_status ON speech_client_user (status);
CREATE INDEX IF NOT EXISTS idx_speech_client_user_client_type ON speech_client_user (client_type);
CREATE INDEX IF NOT EXISTS idx_speech_client_user_create_time ON speech_client_user (create_time);

-- speech_client_usage 表索引
CREATE INDEX IF NOT EXISTS idx_speech_client_usage_client_user_id ON speech_client_usage (client_user_id);
CREATE INDEX IF NOT EXISTS idx_speech_client_usage_date ON speech_client_usage (usage_date);
CREATE INDEX IF NOT EXISTS idx_speech_client_usage_client_date ON speech_client_usage (client_user_id, usage_date);

-- JSONB 字段索引
CREATE INDEX IF NOT EXISTS idx_speech_client_user_ip_whitelist ON speech_client_user USING GIN (ip_whitelist);
CREATE INDEX IF NOT EXISTS idx_speech_client_user_allowed_models ON speech_client_user USING GIN (allowed_models);
CREATE INDEX IF NOT EXISTS idx_speech_client_usage_model_usage ON speech_client_usage USING GIN (model_usage);
CREATE INDEX IF NOT EXISTS idx_speech_client_usage_language_usage ON speech_client_usage USING GIN (language_usage);

-- ======================================
-- 第四部分：添加约束
-- ======================================

-- speech_record 表约束
DO $$
BEGIN
    -- 用户类型约束
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'check_user_type') THEN
        ALTER TABLE speech_record ADD CONSTRAINT check_user_type 
            CHECK (user_type IN ('system', 'client'));
    END IF;
    
    -- 回调状态约束
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'check_callback_status') THEN
        ALTER TABLE speech_record ADD CONSTRAINT check_callback_status 
            CHECK (callback_status IN ('pending', 'sent', 'failed'));
    END IF;
    
    -- 用户一致性约束
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'check_user_consistency') THEN
        ALTER TABLE speech_record ADD CONSTRAINT check_user_consistency 
            CHECK (
                (user_type = 'system' AND user_id IS NOT NULL AND client_user_id IS NULL) OR
                (user_type = 'client' AND user_id IS NULL AND client_user_id IS NOT NULL)
            );
    END IF;
END $$;

-- speech_client_user 表约束
DO $$
BEGIN
    -- 客户端类型约束
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'check_client_type') THEN
        ALTER TABLE speech_client_user ADD CONSTRAINT check_client_type 
            CHECK (client_type IN ('personal', 'enterprise', 'developer'));
    END IF;
    
    -- 客户端状态约束
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'check_client_status') THEN
        ALTER TABLE speech_client_user ADD CONSTRAINT check_client_status 
            CHECK (status IN ('active', 'suspended', 'inactive'));
    END IF;
    
    -- 月度配额约束
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'check_monthly_quota') THEN
        ALTER TABLE speech_client_user ADD CONSTRAINT check_monthly_quota 
            CHECK (monthly_quota >= 0);
    END IF;
    
    -- 已使用配额约束
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'check_used_quota') THEN
        ALTER TABLE speech_client_user ADD CONSTRAINT check_used_quota 
            CHECK (used_quota >= 0 AND used_quota <= monthly_quota);
    END IF;
    
    -- 频率限制约束
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'check_rate_limits') THEN
        ALTER TABLE speech_client_user ADD CONSTRAINT check_rate_limits 
            CHECK (rate_limit_per_minute > 0 AND rate_limit_per_hour > 0);
    END IF;
END $$;

-- speech_client_usage 表约束
DO $$
BEGIN
    -- 使用次数约束
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'check_usage_counts') THEN
        ALTER TABLE speech_client_usage ADD CONSTRAINT check_usage_counts 
            CHECK (request_count >= 0 AND success_count >= 0 AND failed_count >= 0);
    END IF;
    
    -- 成功失败次数约束
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'check_success_failed_sum') THEN
        ALTER TABLE speech_client_usage ADD CONSTRAINT check_success_failed_sum 
            CHECK (success_count + failed_count <= request_count);
    END IF;
    
    -- 计费金额约束
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints 
                   WHERE constraint_name = 'check_billing_amount') THEN
        ALTER TABLE speech_client_usage ADD CONSTRAINT check_billing_amount 
            CHECK (billing_amount >= 0);
    END IF;
END $$;

-- ======================================
-- 第五部分：添加外键约束
-- ======================================

-- 添加外键约束（仅在约束不存在时）
DO $$
BEGIN
    -- speech_record 到 speech_client_user 的外键
    IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints 
                   WHERE constraint_name = 'fk_speech_record_client_user') THEN
        ALTER TABLE speech_record ADD CONSTRAINT fk_speech_record_client_user 
            FOREIGN KEY (client_user_id) REFERENCES speech_client_user(id) ON DELETE RESTRICT;
    END IF;
    
    -- speech_client_usage 到 speech_client_user 的外键
    IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints 
                   WHERE constraint_name = 'fk_speech_client_usage_client_user') THEN
        ALTER TABLE speech_client_usage ADD CONSTRAINT fk_speech_client_usage_client_user 
            FOREIGN KEY (client_user_id) REFERENCES speech_client_user(id) ON DELETE CASCADE;
    END IF;
END $$;

-- ======================================
-- 第六部分：创建触发器和函数
-- ======================================

-- 1. 创建更新时间戳的触发器函数
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 2. 为新表添加触发器
DROP TRIGGER IF EXISTS update_speech_client_user_modtime ON speech_client_user;
CREATE TRIGGER update_speech_client_user_modtime 
    BEFORE UPDATE ON speech_client_user 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

DROP TRIGGER IF EXISTS update_speech_client_usage_modtime ON speech_client_usage;
CREATE TRIGGER update_speech_client_usage_modtime 
    BEFORE UPDATE ON speech_client_usage 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- 3. 创建配额重置触发器函数
CREATE OR REPLACE FUNCTION reset_monthly_quota()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查是否需要重置配额
    IF NEW.quota_reset_date <= CURRENT_DATE THEN
        NEW.used_quota = 0;
        NEW.quota_reset_date = (CURRENT_DATE + INTERVAL '1 month')::DATE;
    END IF;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 4. 添加配额重置触发器
DROP TRIGGER IF EXISTS reset_client_quota_trigger ON speech_client_user;
CREATE TRIGGER reset_client_quota_trigger 
    BEFORE UPDATE ON speech_client_user 
    FOR EACH ROW EXECUTE FUNCTION reset_monthly_quota();

-- ======================================
-- 第七部分：添加表注释
-- ======================================

-- 表注释
COMMENT ON TABLE speech_client_user IS '语音识别客户端用户表';
COMMENT ON TABLE speech_client_usage IS '客户端用户使用统计表';

-- speech_client_user 字段注释
COMMENT ON COLUMN speech_client_user.id IS '客户端用户ID';
COMMENT ON COLUMN speech_client_user.user_name IS '用户名称';
COMMENT ON COLUMN speech_client_user.email IS '邮箱地址';
COMMENT ON COLUMN speech_client_user.client_type IS '客户端类型：personal-个人, enterprise-企业, developer-开发者';
COMMENT ON COLUMN speech_client_user.status IS '状态：active-激活, suspended-暂停, inactive-停用';
COMMENT ON COLUMN speech_client_user.api_key IS 'API密钥';
COMMENT ON COLUMN speech_client_user.api_secret_hash IS 'API密钥哈希值';
COMMENT ON COLUMN speech_client_user.monthly_quota IS '月度配额（分钟）';
COMMENT ON COLUMN speech_client_user.used_quota IS '已使用配额（分钟）';
COMMENT ON COLUMN speech_client_user.ip_whitelist IS 'IP白名单（JSON格式）';
COMMENT ON COLUMN speech_client_user.allowed_models IS '允许使用的模型列表（JSON格式）';

-- speech_client_usage 字段注释
COMMENT ON COLUMN speech_client_usage.client_user_id IS '客户端用户ID';
COMMENT ON COLUMN speech_client_usage.usage_date IS '统计日期';
COMMENT ON COLUMN speech_client_usage.model_usage IS '模型使用统计，格式：{"sensevoice-small": {"count": 10, "duration": 120.5}}';
COMMENT ON COLUMN speech_client_usage.language_usage IS '语言使用统计，格式：{"zh": 50, "en": 30}';

-- speech_record 新字段注释
COMMENT ON COLUMN speech_record.client_user_id IS '客户端用户ID';
COMMENT ON COLUMN speech_record.user_type IS '用户类型：system-系统用户, client-客户端用户';
COMMENT ON COLUMN speech_record.api_request_id IS 'API请求ID';
COMMENT ON COLUMN speech_record.callback_url IS '回调URL';
COMMENT ON COLUMN speech_record.callback_status IS '回调状态：pending-待回调, sent-已发送, failed-回调失败';

-- ======================================
-- 第八部分：初始化数据
-- ======================================

-- 1. 创建示例客户端用户（可选，用于测试）
-- 注意：在生产环境中应该删除或修改这部分

INSERT INTO speech_client_user (
    user_name, 
    email, 
    company, 
    client_type, 
    api_key, 
    api_secret_hash, 
    monthly_quota,
    quota_reset_date,
    allowed_models,
    ip_whitelist,
    create_by
) VALUES (
    'Demo Client',
    'demo@example.com',
    'Demo Company',
    'enterprise',
    'ak_demo_12345678901234567890123456',
    encode(digest('demo_secret_12345678901234567890' || 'speech_salt_2024', 'sha256'), 'hex'),
    5000,
    (CURRENT_DATE + INTERVAL '1 month')::DATE,
    '["sensevoice-small", "paraformer-zh"]'::jsonb,
    '["0.0.0.0/0"]'::jsonb,
    'system'
) ON CONFLICT (email) DO NOTHING;

-- ======================================
-- 第九部分：数据完整性检查
-- ======================================

-- 检查数据完整性
DO $$
DECLARE
    record_count INTEGER;
    user_count INTEGER;
BEGIN
    -- 检查 speech_record 表中的数据完整性
    SELECT COUNT(*) INTO record_count FROM speech_record WHERE user_type = 'system' AND user_id IS NULL;
    IF record_count > 0 THEN
        RAISE WARNING '发现 % 条系统用户记录的 user_id 为空，请检查数据完整性', record_count;
    END IF;
    
    SELECT COUNT(*) INTO record_count FROM speech_record WHERE user_type = 'client' AND client_user_id IS NULL;
    IF record_count > 0 THEN
        RAISE WARNING '发现 % 条客户端用户记录的 client_user_id 为空，请检查数据完整性', record_count;
    END IF;
    
    -- 检查客户端用户表
    SELECT COUNT(*) INTO user_count FROM speech_client_user;
    RAISE NOTICE '已创建 % 个客户端用户', user_count;
    
    RAISE NOTICE '数据库迁移完成！';
END $$;

COMMIT;

-- ======================================
-- 迁移脚本结束
-- ======================================

-- 迁移后的建议操作：
-- 1. 运行 ANALYZE 命令更新统计信息
-- 2. 检查应用程序的连接和权限
-- 3. 测试新的双用户体系功能
-- 4. 监控性能指标

-- 执行统计信息更新
ANALYZE speech_record;
ANALYZE speech_client_user;
ANALYZE speech_client_usage; 